
const fs = require("fs")
const sqlite3 = require("sqlite3").verbose()
const os = require("os")
const NODE_ENV = process.env.NODE_ENV
const userDir = os.homedir()
const dbFolder = userDir + (NODE_ENV === 'development' ? "/.lwtest/":"/.lw")
import { QrySqliteTables, QrySqliteTableInfo } from "../../renderer/src/constants"
import { add_tables,add_indexs,alter_tables } from "./tables"
const globleColumnsMap = {}


console.log(dbFolder)

if (!fs.existsSync(dbFolder)) {
    fs.mkdirSync(dbFolder)
} 


const db = new sqlite3.Database(dbFolder+"/local.db")

//初始化表结构到内存
const initTableColumnsMap = async ()=>{
    let tables = await queryAll(QrySqliteTables,[])
    for(let i=0; i<tables.length; i++){
        let columns = await queryAll(QrySqliteTableInfo.replace('?',tables[i].name), [])
        const columnMap = {}
        for(let j=0; j<columns.length; j++){
            columnMap[toCamelCase(columns[j].name)] = columns[j].name
        }

        globleColumnsMap[tables[i].name] = columnMap 
    }
}

//创建表
const createTable = ()=> {
    return new Promise( async (resolve,reject) => {
        for(const item of add_tables){
            console.log(item)
            await db.run(item)
        }

        for(const item of add_indexs){
            await db.run(item)
        }

        for(const item of alter_tables){
            const filedList = await queryAll(QrySqliteTableInfo.replace('?',item.tableName),[])
            const field = filedList.some(row=> row.name === item.field)
            if(!field){
                await db.run(item.sql)
            }
        }
        resolve()
    })
}

//查询数据
const queryAll = (sql,params)=>{
    return new Promise((resolve,reject)=>{
        const stmt = db.prepare(sql)
        stmt.all(params, function(err,row){
            if(err){
                resolve([])
            }
            row.forEach((item,index)=>{
                row[index] = convertDbObj2BizObj(item)
            })
            resolve(row)
        })
        stmt.finalize();
    })
}

//查询返回记录数
const queryCount = (sql,params)=>{
    return new Promise((resolve,reject)=>{
        const stmt = db.prepare(sql)
        stmt.get(params, function(err,row){
            if(err){
                resolve(0)
            }
            resolve(Array.from(Object.values(row))[0])
        })
        stmt.finalize();
    })
}

//查询单条数据
const queryOne = (sql,params)=>{
    return new Promise((resolve,reject)=>{
        const stmt = db.prepare(sql)
        console.log('查询语句：'+sql+";参数："+params)
        stmt.get(params, function(err,row){
            if(err){
                resolve({})
            }
            resolve(convertDbObj2BizObj(row))
        })
        stmt.finalize();
    })
}

//run 方法
const run = (sql,params)=>{
    return new Promise((resolve,reject)=>{
        const stmt = db.prepare(sql)
        
        stmt.run(params, function(err,row){
            if(err){
                resolve("操作数据失败")
            }
            resolve(this.changes)
        })
        stmt.finalize();
    })
}

//insert 方法
const insert = (sqlPrefix,tableName,data)=>{
    const columsMap = globleColumnsMap[tableName]
    const dbColums = []
    const params = []
    for(let item in data){
        if(data[item]!=undefined && columsMap[item]!=undefined){
            dbColums.push(columsMap[item])
            params.push(data[item])
        }
    }
    const prepare = "?".repeat(dbColums.length).split("").join(",")
    const sql = `${sqlPrefix} ${tableName}(${dbColums.join(",")})values(${prepare})`
    return run(sql,params)

}

const insertOrReplace = (tableName,data)=>{
    return insert("insert or replace into",tableName,data)
}

const insertOrIgnore = (tableName,data)=>{
    return insert("insert or ignore into ",tableName,data)
}


//update 操作
const update = (tableName,data,paramsData)=>{
    const columsMap = globleColumnsMap[tableName]
    const dbColums = []
    const params = []
    const whereColumns = []
    for(let item in data){
        if(data[item]!=undefined && columsMap[item]!=undefined){
            dbColums.push(`${columsMap[item]}=?`)
            params.push(data[item])
        }
    }

    for(let item in paramsData){
        if(paramsData[item]){
            params.push(paramsData[item])
            whereColumns.push(`${columsMap[item]}=?`)
        }
    }

    const sql = `update  ${tableName} set ${dbColums.join(",")} ${whereColumns.length>0? "where ":""} ${whereColumns.join(" and ")}`
    return run(sql,params)

}

//db 对象转 驼峰后的对象
const convertDbObj2BizObj = (data) =>{
    if(!data){
        return null
    }
    const bizData = {}
    for(let item in data){
        bizData[toCamelCase(item)] = data[item]
    }
    return bizData
}

//驼峰转换
const toCamelCase = (str) => {
    let s =
    str &&
    str
      .match(/[A-Z]{2,}(?=[A-Z][a-z]+[0-9]*|\b)|[A-Z]?[a-z]+[0-9]*|[A-Z]|[0-9]+/g)
      .map(x => x.slice(0, 1).toUpperCase() + x.slice(1).toLowerCase())
      .join('');
  return s.slice(0, 1).toLowerCase() + s.slice(1);
}

//初始化数据库
const init = ()=>{
    db.serialize( async()=>{
        await createTable()
        await initTableColumnsMap()
    })
}

init()

export {
    run,
    queryOne,
    queryCount,
    queryAll,
    insert,
    insertOrIgnore,
    insertOrReplace,
    update
}